package com.example.demo.service.impl;

import java.io.InputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import com.example.demo.mapper.TeachingPlanMapper;
import com.example.demo.model.Teachingplan;
import com.example.demo.service.AdminService;

@Service
public class AdminServiceImpl implements AdminService{
	
	@Autowired
	TeachingPlanMapper teachingplanmapper;
	
	@Override
	public String insertExcel(String filename, MultipartFile file) throws Exception {
System.out.println("调用上传工具类");
		int[] resultcell = new int[] {0,1,2,3,4,5,6,7,8,9,10};
		List<Teachingplan> resultlist = new ArrayList<>();
		  if (!filename.matches("^.+\\.(?i)(xls)$") && !filename.matches("^.+\\.(?i)(xlsx)$")) {
	            return "上传文件格式不正确";
	        }
	        boolean isExcel2003 = true;
	        if (filename.matches("^.+\\.(?i)(xlsx)$")) {
	            isExcel2003 = false;
	        }
	        InputStream is = file.getInputStream();
	        Workbook wb = null;
	        if (isExcel2003) {
	            wb = new HSSFWorkbook(is);
	        } else {
	            wb = new XSSFWorkbook(is);
	        }
	        Sheet sheet = wb.getSheetAt(0);
	        resultlist = getSheetVal(sheet, resultcell);
	        System.out.println("结果集---"+resultlist);
	        try{
	            teachingplanmapper.insertExcle(resultlist);
	   
	        }catch (Exception e){
	            e.printStackTrace();
	            return ("数据导入失败");
	        }

	     return "成功";
	}

	private List<Teachingplan> getSheetVal(Sheet sheet, int[] resultcell) {
System.out.println("getsheetval");
		List<Teachingplan> planList = new ArrayList<>();//返回的结果集
        int[] resultIndex = new int[resultcell.length];//存储需要上传字段的下标
        Teachingplan plan;
        for (int r = 1; r <= sheet.getLastRowNum(); r++) {
        	System.out.println("sheet"+sheet.getLastRowNum());
            Row row = sheet.getRow(r);
            if (row == null) {
                continue;
            }
            plan = new Teachingplan();
            for (int i = 0;i<row.getPhysicalNumberOfCells();i++){
            	System.out.println("i="+i);
            	Cell cell=row.getCell(i);
            	if(cell == null) {
            		System.out.println("为空");
            		continue;
            	}else {
            		String temp = cell.toString().trim();
                    
                    System.out.println(temp);
                    for (int j=0;j<resultcell.length;j++){
                    	
                        if (i==resultcell[j]){
                            switch (i){
                                case 0:
                                	plan.setPlanversion(temp);
                                    break;
                                case 1:
                                	plan.setMajorname(temp);
                                    break;
                                case 2:
                                	plan.setCoursecode(temp);
                                    break;
                                case 3:
                                	plan.setCoursetype(temp);
                                	break;
                                case 4:
                                	plan.setSubtype(temp);
                                	break;
                                case 5:
                                	BigDecimal bl=new BigDecimal(temp);
                                	temp=bl.toPlainString();
                                	plan.setCourseid(temp);
                                	break;
                                case 6:
                                	plan.setCoursename(temp);
                                	break;
                                case 7:
                                	
                                	plan.setCredithour(temp);
                                	break;
                                case 8:
                                	
                                	plan.setTheoryhour(temp);
                                	break;
                                case 9:
                                	
                                	plan.setPracticeperiods(temp);
                                	break;
                                case 10:
                               
                                	plan.setStartsemester(temp);
                                	break;
                                default:
                                    break;
                            }
                        }else{
                            continue;
                        }
                    }
            	}
                
            }
            System.out.println("addplan");
            planList.add(plan);
        }
        return planList;
	}

	@Override
	public List<Teachingplan> selectAllTeachingPlan() {
		List<Teachingplan> list= teachingplanmapper.selectAllTeachingPlan();
		return list;
	}

}
